﻿using Asxsyd92Core.Utils.Provider;
using Asxsyd92Core.Utils.SQLServer;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using Xciles.NetStandardValueInjecter;

namespace Asxsyd92Core.Utils.Data.SQLServer
{
    public static class SqlFromData
    {
        static string cs = SqlEasy.connString; //数据库连接字符串
        public static Guid SaveFromData(string table, string json)
        {
            try
            {
                Guid ID=Guid.Empty;
                string cs = SqlEasy.connString; //数据库连接字符串
                string sql = string.Empty, dbtablepk = "ID";
                List<System.Data.IDataParameter> parList = new List<System.Data.IDataParameter>();
                var jsonData = LitJSONCore.JsonMapper.ToObject(json);
                using (SqlConnection conn = new SqlConnection(cs))
                {
                    if (jsonData["ID"] == null) { return Guid.Empty; }

                    var _ID = jsonData["ID"].ToString();
                    sql = string.Format("SELECT * FROM {0} WHERE {1}=@pk", table, dbtablepk);
                    parList.Add(new System.Data.SqlClient.SqlParameter("@pk", _ID));
                    System.Data.IDbDataAdapter dataAdapter = GetDataAdapter(sql, conn, parList.ToArray());
                    System.Data.DataSet ds = new System.Data.DataSet();
                    dataAdapter.Fill(ds);
                    System.Data.DataTable schemaDt = GetTableSchema(conn, table);
                    System.Data.DataTable dt = ds.Tables[0];
                    bool isNew = dt.Rows.Count == 0;
                    if (isNew)
                    {
                        dt.Rows.Add(dt.NewRow());
                    }
                    #region 保存主表数据
                    string maxfiled = string.Empty;//保存当前流水号的字段
                    int maxNumber = 0;//当前流水号值


                    for (int i = 0; i < dt.Columns.Count; i++)
                    {
                        string colnumName = dt.Columns[i].ColumnName;
                        // string name = string.Concat(table, ".", colnumName);
                        string value = "";
                        try
                        {
                            //如果json中没有字段则设置为数据库中的值
                            value = jsonData[colnumName].ToString();
                        }
                        catch
                        {

                            value = dt.Rows[0][colnumName].ToString();
                        }
                        //System.Web.HttpContext.Current.Request.Form[name];
                        if (string.Compare(colnumName, dbtablepk, true) == 0)
                        {
                            if (value == Guid.Empty.ToString())
                            {
                                continue;
                            }
                            else
                            {
                                ID = value.ToGuid();
                            }
                        }
                        var colnum = dt.Columns[i];
                        string colnumDataType = colnum.DataType.FullName;
                        object defaultValue;

                        System.Data.DataRow[] schemaDrs = schemaDt.Select(string.Format("f_name='{0}'", colnumName));
                        bool hasDefault = false;//列是否有默认值
                        bool hasNull = false;//列是否可以为空
                        bool isSet = getColnumIsValue(colnumDataType, value, out defaultValue);
                        object dbDefaultValue = string.Empty;//数据库表默认值

                        hasDefault = schemaDrs.Length > 0 && schemaDrs[0]["cdefault"].ToString() != "0";
                        hasNull = schemaDrs.Length > 0 && schemaDrs[0]["is_null"].ToString() != "0";
                        if (hasDefault && !schemaDrs[0]["defaultvalue"].ToString().IsNullOrEmpty())
                        {
                            dbDefaultValue = GetDbDefaultValue_SqlServer(schemaDrs[0]["defaultvalue"].ToString().Trim());
                        }

                        if (maxfiled.IsNullOrEmpty() || !colnumName.Equals(maxfiled, StringComparison.CurrentCultureIgnoreCase))//这里做判断主要是为了二次保存时不将存储编号的字段更新为空
                        {
                            if (isSet)
                            {

                                dt.Rows[0][colnumName] = value;
                            }
                            else
                            {
                                if (!hasDefault)
                                {
                                    if (hasNull)
                                    {
                                        dt.Rows[0][colnumName] = DBNull.Value;

                                    }
                                    else
                                    {
                                        dt.Rows[0][colnumName] = defaultValue;

                                    }
                                }
                                else
                                {
                                    if (dbDefaultValue != null)
                                    {
                                        try
                                        {
                                            dt.Rows[0][colnumName] = dbDefaultValue;

                                        }
                                        catch
                                        { }
                                    }
                                }
                            }
                        }
                    }
                    //如果设置了最大流水号字段，则这里要将当前流水号值保存到这个字段
                    if (!maxfiled.IsNullOrEmpty())
                    {
                        dt.Rows[0][maxfiled] = maxNumber;
                    }
                    #endregion

                    #region 设置主键值
                    bool isIdentity = false;
                    if (isNew)
                    {
                        if (ID == Guid.Empty)
                        {
                            var pkColnum = dt.Columns[dbtablepk];
                            System.Data.DataRow[] schemaDrs = schemaDt.Select(string.Format("f_name='{0}'", dbtablepk));
                            if (schemaDrs.Length > 0)
                            {
                                isIdentity = false;
                                bool isDefault = false;
                                bool isGuid = false;
                                isIdentity = schemaDrs[0]["isidentity"].ToString() == "1";
                                isDefault = schemaDrs[0]["cdefault"].ToString() != "0";
                                isGuid = pkColnum.DataType.FullName == "System.Guid";
                                if (!isIdentity && isGuid)
                                {
                                    ID = Guid.NewGuid();
                                    dt.Rows[0][dbtablepk] = ID;
                                }
                            }
                        }
                        else
                        {
                            //dt.Rows[0][dbtablepk] = instanceid;
                        }
                    }
                    #endregion

                    #region 执行保存
                    System.Data.SqlClient.SqlCommandBuilder scb = new System.Data.SqlClient.SqlCommandBuilder((System.Data.SqlClient.SqlDataAdapter)dataAdapter);
                    dataAdapter.Update(ds);
                    scb.Dispose();
                    #endregion

                    #region 如果是新增，又是自增列则查询刚插入的自增列值
                    if (isNew && isIdentity)
                    {
                        string identitysql = "SELECT @@IDENTITY";
                        using (System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand(identitysql, (System.Data.SqlClient.SqlConnection)conn))
                        {
                            object obj = cmd.ExecuteScalar();
                            if (obj != null)
                            {
                                ID = obj.ToString().ToGuid();
                                dt.Rows[0][dbtablepk] = ID;
                            }
                        }
                    }
                    #endregion
                    return ID;
                }
            }
            catch (Exception ex)
            {
                Asxsyd92Core.Utils.Tools.createFile(ex.Message);
                return Guid.Empty;
            }



        }
        /// <summary>
        /// 保存表单数据
        /// </summary>
        /// <param name="data">json中需要携带tab；表名，ID携带</param>
        /// <returns></returns>
        public static Guid Save(string data, out string msg)
        {
            if (data != "" && data != "\"\"")
            {
                try
                {
                    var obj = Newtonsoft.Json.JsonConvert.DeserializeObject<Dictionary<object, object>>(data);
                    if (obj != null)
                    {
                        var da = obj.FirstOrDefault(c => c.Key.ToString().ToLower() == "tab".ToLower()).Value;
                        if (da == null)
                        {
                            msg = "tab中不能为空";
                            return Guid.Empty;
                        }
                        var daID = obj.FirstOrDefault(c => c.Key.ToString().ToLower() == "ID".ToLower()).Value;
                        if (daID == null)
                        {
                            msg = "主键不在json中";
                            return Guid.Empty;
                        }
                        Guid ID=Guid.Empty;
                        string cs = SqlEasy.connString; //数据库连接字符串
                        string sql = string.Empty, dbtablepk = "ID";
                        string table = obj["tab"].ToString().ToLower();
                        List<System.Data.IDataParameter> parList = new List<System.Data.IDataParameter>();
                        using (SqlConnection conn = new SqlConnection(cs))
                        {
                            var _ID = obj["ID"].ToString().ToLower();
                            sql = string.Format("SELECT * FROM {0} WHERE {1}=@pk", table, dbtablepk);
                            parList.Add(new System.Data.SqlClient.SqlParameter("@pk", _ID));
                            System.Data.IDbDataAdapter dataAdapter = GetDataAdapter(sql, conn, parList.ToArray());
                            System.Data.DataSet ds = new System.Data.DataSet();
                            dataAdapter.Fill(ds);
                            System.Data.DataTable schemaDt = GetTableSchema(conn, table);
                            System.Data.DataTable dt = ds.Tables[0];
                            bool isNew = dt.Rows.Count == 0;
                            if (isNew)
                            {
                                dt.Rows.Add(dt.NewRow());
                            }
                            #region 保存主表数据
                            string maxfiled = string.Empty;//保存当前流水号的字段
                            int maxNumber = 0;//当前流水号值


                            for (int i = 0; i < dt.Columns.Count; i++)
                            {
                                //将其转化为小写
                                string colnumName = dt.Columns[i].ColumnName.ToLower();
                                // string name = string.Concat(table, ".", colnumName);
                                string value = "";
                                try
                                {
                                    //如果json中没有字段则设置为数据库中的值
                                    value = obj[colnumName].ToString();
                                }
                                catch
                                {

                                    value = dt.Rows[0][colnumName].ToString();
                                }
                                //System.Web.HttpContext.Current.Request.Form[name];
                                if (string.Compare(colnumName, dbtablepk, true) == 0)
                                {
                                    if (value == Guid.Empty.ToString())
                                    {
                                        continue;
                                    }
                                    else
                                    {
                                        ID = value.ToGuid();
                                    }
                                }
                                var colnum = dt.Columns[i];
                                string colnumDataType = colnum.DataType.FullName;
                                object defaultValue;

                                System.Data.DataRow[] schemaDrs = schemaDt.Select(string.Format("f_name='{0}'", colnumName));
                                bool hasDefault = false;//列是否有默认值
                                bool hasNull = false;//列是否可以为空
                                bool isSet = getColnumIsValue(colnumDataType, value, out defaultValue);
                                object dbDefaultValue = string.Empty;//数据库表默认值

                                hasDefault = schemaDrs.Length > 0 && schemaDrs[0]["cdefault"].ToString() != "0";
                                hasNull = schemaDrs.Length > 0 && schemaDrs[0]["is_null"].ToString() != "0";
                                if (hasDefault && !schemaDrs[0]["defaultvalue"].ToString().IsNullOrEmpty())
                                {
                                    dbDefaultValue = GetDbDefaultValue_SqlServer(schemaDrs[0]["defaultvalue"].ToString().Trim());
                                }

                                if (maxfiled.IsNullOrEmpty() || !colnumName.Equals(maxfiled, StringComparison.CurrentCultureIgnoreCase))//这里做判断主要是为了二次保存时不将存储编号的字段更新为空
                                {
                                    if (isSet)
                                    {

                                        dt.Rows[0][colnumName] = value;
                                    }
                                    else
                                    {
                                        if (!hasDefault)
                                        {
                                            if (hasNull)
                                            {
                                                dt.Rows[0][colnumName] = DBNull.Value;

                                            }
                                            else
                                            {
                                                dt.Rows[0][colnumName] = defaultValue;

                                            }
                                        }
                                        else
                                        {
                                            if (dbDefaultValue != null)
                                            {
                                                try
                                                {
                                                    dt.Rows[0][colnumName] = dbDefaultValue;

                                                }
                                                catch
                                                { }
                                            }
                                        }
                                    }
                                }
                            }
                            //如果设置了最大流水号字段，则这里要将当前流水号值保存到这个字段
                            if (!maxfiled.IsNullOrEmpty())
                            {
                                dt.Rows[0][maxfiled] = maxNumber;
                            }
                            #endregion

                            #region 设置主键值
                            bool isIdentity = false;
                            if (isNew)
                            {
                                if (ID == Guid.Empty)
                                {
                                    var pkColnum = dt.Columns[dbtablepk];
                                    System.Data.DataRow[] schemaDrs = schemaDt.Select(string.Format("f_name='{0}'", dbtablepk));
                                    if (schemaDrs.Length > 0)
                                    {
                                        isIdentity = false;
                                        bool isDefault = false;
                                        bool isGuid = false;
                                        isIdentity = schemaDrs[0]["isidentity"].ToString() == "1";
                                        isDefault = schemaDrs[0]["cdefault"].ToString() != "0";
                                        isGuid = pkColnum.DataType.FullName == "System.Guid";
                                        if (!isIdentity && isGuid)
                                        {
                                            ID = Guid.NewGuid();
                                            dt.Rows[0][dbtablepk] = ID;
                                        }
                                    }
                                }
                                else
                                {
                                    //dt.Rows[0][dbtablepk] = instanceid;
                                }
                            }
                            #endregion

                            #region 执行保存
                            System.Data.SqlClient.SqlCommandBuilder scb = new System.Data.SqlClient.SqlCommandBuilder((System.Data.SqlClient.SqlDataAdapter)dataAdapter);
                            dataAdapter.Update(ds);
                            scb.Dispose();
                            #endregion

                            #region 如果是新增，又是自增列则查询刚插入的自增列值
                            if (isNew && isIdentity)
                            {
                                string identitysql = "SELECT @@IDENTITY";
                                using (System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand(identitysql, (System.Data.SqlClient.SqlConnection)conn))
                                {
                                    object objs = cmd.ExecuteScalar();
                                    if (objs != null)
                                    {
                                        ID = objs.ToString().ToGuid();
                                        dt.Rows[0][dbtablepk] = ID;
                                    }
                                }
                            }
                            #endregion
                            msg = ID == Guid.Empty ? "保存失败！" : "保存成功！";
                            return ID;
                        }
                    }
                    else
                    {
                        msg = "Json解析失败！";

                        return Guid.Empty;
                    }

                }
                catch (Exception ex)
                {
                    msg = ex.Message;

                    Asxsyd92Core.Utils.Tools.createFile(ex.Message);
                    return Guid.Empty;
                }


            }
            else
            {
                msg = "json格式错误";

                Asxsyd92Core.Utils.Tools.createFile("json格式错误");
                return Guid.Empty;

            }

        }
        public static IEnumerable<object> GetFromData(string table, Guid ID)
        {
            using (var conn = new SqlConnection(cs))
            {
                using (var cmd = conn.CreateCommand())
                {
                    cmd.CommandType = CommandType.Text;
                    cmd.CommandText = "select * from " + table + " where ID=@ID";
                    cmd.InjectFrom<SetParamsValues>(new { ID = ID });
                    conn.Open();

                    using (var dr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
                    {
                        DataTable tables = new DataTable();
                        while (dr.Read())
                        {
                            var o = TableObjctToList(dr);
                            yield return o;
                        }
                        dr.Close();
                    }
                }
            }

        }
        /// <summary>
        /// 获取数据
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public static IEnumerable<object> GetList(string sql)
        {
            using (var conn = new SqlConnection(cs))
            {
                using (var cmd = conn.CreateCommand())
                {
                    cmd.CommandType = CommandType.Text;
                    cmd.CommandText = sql;

                    conn.Open();

                    using (var dr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
                    {
                        DataTable tables = new DataTable();
                        while (dr.Read())
                        {
                            var o = TableObjctToList(dr);
                            yield return o;
                        }
                        dr.Close();
                    }
                }
            }

        }
        /// <summary>
        /// 获取数据
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public static IEnumerable<object> GetList(string sql, object where)
        {
            using (var conn = new SqlConnection(cs))
            {
                using (var cmd = conn.CreateCommand())
                {
                    cmd.CommandType = CommandType.Text;
                    cmd.CommandText = sql;
                    cmd.InjectFrom(new FieldsBy()
                            .SetFormat("{0}=@{0}")
                            .SetNullFormat("{0} is null")
                            .SetGlue("and"),
                            where);
                    cmd.InjectFrom<SetParamsValues>(where);
                    conn.Open();

                    using (var dr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
                    {
                        DataTable tables = new DataTable();
                        while (dr.Read())
                        {
                            var o = TableObjctToList(dr);
                            yield return o;
                        }
                        dr.Close();
                    }
                }
            }

        }
        public static IEnumerable<object> GetFromData(string table, object where)
        {
            using (var conn = new SqlConnection(cs))
            {
                using (var cmd = conn.CreateCommand())
                {
                    cmd.CommandType = CommandType.Text;
                    cmd.CommandText = "select * from " + table + " where "
                        .InjectFrom(new FieldsBy()
                            .SetFormat("{0}=@{0}")
                            .SetNullFormat("{0} is null")
                            .SetGlue("and"),
                            where);
                    cmd.InjectFrom<SetParamsValues>(where);
                    conn.Open();

                    using (var dr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
                    {
                        DataTable tables = new DataTable();
                        while (dr.Read())
                        {
                            var o = TableObjctToList(dr);
                            yield return o;
                        }
                        dr.Close();
                    }
                }
            }
        }

        public static int DelFromData(string table, Guid key)
        {
            try
            {
                using (var conn = new SqlConnection(cs))
                {
                    using (var cmd = conn.CreateCommand())
                    {
                        cmd.CommandType = CommandType.Text;
                        cmd.CommandText = "delete from " + table + " where @ID=ID";
                        cmd.InjectFrom<SetParamsValues>(new { ID = key });
                        conn.Open();
                        var retval = cmd.ExecuteNonQuery();
                        conn.Close();
                        return retval;
                    }
                }
            }
            catch (Exception ex)
            {
                return 0;
            }

        }
        public static int DelFromData(string table, object where)
        {
            using (var conn = new SqlConnection(cs))
            {
                using (var cmd = conn.CreateCommand())
                {
                    cmd.CommandType = CommandType.Text;
                    cmd.CommandText = "delete from " + table + " where "
                        .InjectFrom(new FieldsBy()
                            .SetFormat("{0}=@{0}")
                            .SetNullFormat("{0} is null")
                            .SetGlue("and"),
                            where);

                    cmd.InjectFrom<SetParamsValues>(where);
                    conn.Open();
                    var retval = cmd.ExecuteNonQuery();
                    conn.Close();
                    return retval;
                }
            }
        }
        /// <summary>
        /// 根据条件获取记录
        /// </summary>
        /// <typeparam table="table">表</typeparam>
        /// <param name="page">页码</param>
        /// <param name="pageSize">每页记录数</param>
        /// <param name="sort">排序 如：ID desc </param>
        /// <param name="where">查询条件</param>
        /// <param name="wheretag">默认=</param>
        /// <returns></returns>
        public static IEnumerable<object> GetPage(string table, long page, long pageSize, string sort, object where, string wheretag = "=")
        {
            using (var conn = new SqlConnection(cs))
            {
                using (var cmd = conn.CreateCommand())
                {


                    if (string.IsNullOrEmpty(sort))
                        sort = "ID desc";

                    var sl = cmd.CommandText.InjectFrom(new FieldsBy()
                        .SetFormat("{0} " + wheretag + " @{0}")
                        .SetNullFormat("{0} is null")
                        .SetGlue("and"),
                        where);

                    cmd.CommandType = CommandType.Text;
                    cmd.CommandText = "with result as(select *, ROW_NUMBER() over(order by {3}) nr from {0} where " + sl + @")
                 
                     select  * ,(select count(*)  from {0}  where  " + sl + @")as Count  
                    from    result
                    where   nr  between (({1} - 1) * {2} + 1)
                            and ({1} * {2}) ";

                    cmd.CommandText = string.Format(cmd.CommandText, table, page, pageSize, sort);
                    cmd.InjectFrom<SetParamsValues>(where);
                    conn.Open();

                    using (var dr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
                    {
                        DataTable tables = new DataTable();
                        while (dr.Read())
                        {
                            var o = TableObjctToList(dr);
                            yield return o;
                        }
                        dr.Close();
                    }
                }
            }



        }



        /// <summary>
        /// 默认为ID 倒序排序
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="page">页码</param>
        /// <param name="pageSize">每页记录数</param>
        /// <returns></returns>
        public static IEnumerable<object> GetPage(string table, long page, long pageSize, string sort = "ID desc")
        {
            using (var conn = new SqlConnection(cs))
            {
                using (var cmd = conn.CreateCommand())
                {
                    cmd.CommandType = CommandType.Text;
                    cmd.CommandText = string.Format(@"with result as(select *, ROW_NUMBER() over(order by {3}) nr
                            from {0} )
                    select  * ,(select count(*)  from {0} )as Count  
                    from    result
                    where   nr  between (({1} - 1) * {2} + 1)
                            and ({1} * {2}) ", table, page, pageSize, sort);
                    conn.Open();
                    using (var dr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
                    {
                        DataTable tables = new DataTable();
                        while (dr.Read())
                        {
                            var o = TableObjctToList(dr);
                            yield return o;
                        }
                        dr.Close();
                    }
                }
            }
        }
        /// <summary>
        /// 没有实体返回List
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="table">表名</param>
        /// <param name="where">SetTable类型的JSON</param>
        /// <returns></returns>
        public static IEnumerable<object> GetNoMoldeWhere<T>(string table, string where) where T : new()
        {
            using (var conn = new SqlConnection(cs))
            {
                using (var cmd = conn.CreateCommand())
                {
                    cmd.CommandType = CommandType.Text;
                    if (where != null)
                    {
                        var sql = "";
                        var da = Newtonsoft.Json.JsonConvert.DeserializeObject<List<SQLServer.SetTable>>(where);
                        if (da != null)
                        {
                            where = "1=1";
                            foreach (var itme in da)
                            {
                                where += " and " + itme.ColumnsName + " like '%" + itme.ColumnType.Replace("'", "") + "%' ";
                            }
                            sql = "select *from " + table + " where " + where;
                        }
                        else { sql = "select *from " + table; }

                        cmd.CommandText = sql;
                    }

                    // cmd.InjectFrom<SetParamsValues>(where);
                    conn.Open();

                    using (var dr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
                    {
                        DataTable tables = new DataTable();
                        while (dr.Read())
                        {
                            var o = TableObjctToList(dr);
                            yield return o;
                        }
                        dr.Close();
                    }
                }
            }
        }
        /// <summary>
        /// 数据库中的字段转换为DataList，适用于业务表中的
        /// </summary>
        /// <param name="dr"></param>
        /// <returns></returns>
        public static Dictionary<object, object> TableObjctToList(SqlDataReader dr)
        {
            var ret = new Dictionary<object, object>();
            for (int i = 0; i < dr.FieldCount; i++)
            {
                try
                {
                    object Field = dr.GetName(i).ToString();
                    object FieldVaule = dr.GetValue(i) == null ? "" : dr.GetValue(i).ToString();
                    ret.Add(Field, FieldVaule);
                }
                catch
                {
                    string erroe = "";
                    ret.Add(erroe, "转换出现错误");
                }


            }
            return ret;
        }
        public static System.Data.IDbDataAdapter GetDataAdapter(string cmdText, IDbConnection conn, IDataParameter[] parArray)
        {
            IDbDataAdapter dataAdapter = null;
            using (SqlCommand cmd = new SqlCommand(cmdText, (SqlConnection)conn))
            {
                if (parArray != null && parArray.Length > 0)
                {
                    cmd.Parameters.AddRange(parArray);
                }
                dataAdapter = new SqlDataAdapter(cmd);
            }

            return dataAdapter;
        }

        /// <summary>
        /// 得到一个表的结构
        /// </summary>
        /// <param name="conn"></param>
        /// <param name="tableName"></param>
        /// <param name="dbType"></param>
        /// <returns></returns>
        public static System.Data.DataTable GetTableSchema(System.Data.IDbConnection conn, string tableName)
        {
            DataTable dt = new DataTable();
            string sql = string.Format(@"select a.name as f_name,b.name as t_name,a.prec as [length],a.scale,a.isnullable as is_null, a.cdefault as cdefault,COLUMNPROPERTY( OBJECT_ID('{0}'),a.name,'IsIdentity') as isidentity, 
(select top 1 text from sysobjects d inner join syscolumns e on e.id=d.id inner join syscomments f on f.id=e.cdefault 
where d.name='{0}' and e.name=a.name) as defaultvalue from 
                    sys.syscolumns a inner join sys.types b on b.user_type_id=a.xtype 
                    where object_id('{0}')=id order by a.colid", tableName);
            SqlDataAdapter dap = new SqlDataAdapter(sql, (SqlConnection)conn);
            dap.Fill(dt);
            return dt;
        }
        /// <summary>
        /// 判断列是否有值
        /// </summary>
        /// <param name="colnumDataType"></param>
        /// <param name="value"></param>
        /// <param name="defaultValue">默认值</param>
        /// <returns></returns>
        private static bool getColnumIsValue(string colnumDataType, string value, out object defaultValue)
        {
            bool isSet = false;
            defaultValue = null;
            switch (colnumDataType)
            {
                case "System.Int16":
                case "System.Int32":
                case "System.Int64":
                case "System.UInt16":
                case "System.UInt32":
                case "System.UInt64":
                    isSet = value.IsInt();
                    defaultValue = int.MinValue;
                    break;
                case "System.String":
                    isSet = value != null;
                    defaultValue = "";
                    break;
                case "System.Guid":
                    isSet = value.IsGuid();
                    defaultValue = Guid.Empty;
                    break;
                case "System.Decimal":
                    isSet = value.IsDecimal();
                    defaultValue = decimal.MinValue;
                    break;
                case "System.Double":
                case "System.Single":
                    isSet = value.IsDouble();
                    defaultValue = double.MinValue;
                    break;
                case "System.DateTime":
                    isSet = value.IsDateTime();
                    defaultValue = DateTime.Now;
                    break;
                case "System.Object":
                    isSet = value != null;
                    defaultValue = "";
                    break;
                case "System.Boolean":
                    isSet = value != null && (value.ToString().ToLower() == "false"
                        || value.ToString().ToLower() == "true");
                    defaultValue = 0;
                    break;
            }
            return isSet;
        }

        /// <summary>
        /// 得到数据库默认值-sqlserver
        /// </summary>
        /// <param name="text"></param>
        /// <returns></returns>
        public static object GetDbDefaultValue_SqlServer(string text)
        {
            if (text.IsNullOrEmpty())
            {
                return null;
            }
            object value = text;
            if (text.StartsWith("(("))
            {
                value = text.Replace("((", "").Replace("))", "");
            }
            else if (text.StartsWith("('"))
            {
                value = text.Replace("('", "").Replace("')", "");
            }
            else
            {
                switch (text.ToLower())
                {
                    case "(getdate())":
                        value = DateTime.Now;
                        break;
                    case "(newid())":
                        value = Guid.NewGuid();
                        break;
                }
            }
            return value;
        }

        /// <summary>
        /// 传入表获取表结构
        /// </summary>
        /// <param name="table"></param>
        /// <returns></returns>
        public static List<SetTable> GetTableStructure(string table)
        {
            if (table == "" || table == null) { return null; }
            string sql = "SELECT TableName = OBJECT_NAME(c.object_id), "
            + "ColumnsName = c.name,  "
            + "Description = ex.value,  "
            + "ColumnType = t.name,  "
            + "Length = c.max_length "
            + "FROM "
            + "sys.columns c "
            + "LEFT OUTER JOIN "
            + "sys.extended_properties ex "
            + "ON "
            + "ex.major_id = c.object_id "
            + "AND ex.minor_id = c.column_id "
            + "AND ex.name = 'MS_Description' "
            + "left outer join "
            + "systypes t "
            + "on c.system_type_id = t.xusertype "
            + "WHERE "
            + "OBJECTPROPERTY(c.object_id, 'IsMsShipped') = 0 "
            + "AND OBJECT_NAME(c.object_id) = '" + table + "'";
            var da = SetTableDal.Instance.GetList(sql, null).ToList();
            List<SetTable> list = new List<SetTable>();
            foreach (var item in da)
            {
                if (item.ColumnType.Contains("char") || item.ColumnType.Contains("binary") || item.ColumnType.Contains("time"))
                {
                    var k = "";
                    if (item.Length == -1)
                    {
                        k = "(MAX)";
                    }
                    else { k = "(" + item.Length + ")"; }
                    list.Add(new SetTable { ColumnsName = item.ColumnsName, ColumnType = item.ColumnType + k, Description = item.Description, TableName = item.TableName });
                }
                else
                {
                    list.Add(new SetTable { ColumnsName = item.ColumnsName, ColumnType = item.ColumnType, Description = item.Description, TableName = item.TableName });
                }

            }

            return list;
        }
    }
    public class SetTableDal : BaseRepository<SetTable>
    {

        public static SetTableDal Instance
        {
            get { return SingletonProvider<SetTableDal>.Instance; }
        }
    }
    public class SetTable
    {
        public string TableName { set; get; }
        public string ColumnsName { set; get; }
        public string ColumnType { set; get; }
        public string Description { set; get; }
        public long Length { set; get; }
    }
}
